Non-Correlated Subqueries

At times it is useful to create a subquery that is not directly related to the main query. For example, you may want to create a report that includes open vendor invoices but only for those vendors in a certain state within the U.S.

It is possible to accomplish this with some tricky join logic, but a “non-correlated” subquery is often easier. These types of subqueries are generally not tied to a particular field in the main query using the = operator, rather they typically use other operators such as IN, NOT IN, EXISTS, and NOT EXISTS. These are called non-correlated subqueries as the subquery is not really dependent on the main query data. The results are the same regardless of which row is being examined in the main query. This can also result in faster performance for many queries.

In this example using the sample database, the non-correlated subquery will return a list of vendors (vendor ID’s) in the state of Arizona, and the main query will return invoices that have been received (invoice_recd = ‘y’ in the Purchase Order Table). The Vendor_ID field is the common field in both tables. Vendors with vendor_ID of “ven002” and “ven007” are located in Arizona.

Creating the Main Query

The process is essentially the same as used for the Correlated subquery discussed in the previous example.

Create the main query to return all Purchase Orders with invoice_reqd field = “Y”.

The SELECT clause:

The select tab. From the purchase orders table, vendor i d, p o i d, and invoice received have all been added.

And the WHERE clause:

The where tab. From the purchase orders table, invoice received has been added with the condition equal to y.

Creating the Subquery

Click the Add Query button to add the subquery. The subquery finds all vendors located in Arizona. Note that this query is not tied to a particular set of records in the main query like the previous example was. Add 'vendor_ID' from the Vendors table
The where clause. From the vendors table, vendor state has been added with the condition equal to a z.

Placing the Subquery in the Main Query

This query is non-correlated, and is likely to return multiple records, so for this example the “IN” operator is used to link the subquery to the main query. Select Purchase_Orders for the Table, and select vendor_ID for the Field.
The where clause after adding the sub query to the main query. In has been selected from the radio buttons. Each radio button selects a different boolean operator. The table selected is purchase orders, and the selected field is vendor i d.

This results in the following query:

SELECT Purchase_Orders.vendor_ID,
Purchase_Orders.PO_ID,
Purchase_Orders.invoice_recd
FROM Purchase_Orders
WHERE Purchase_Orders.invoice_recd ='y' AND Purchase_Orders.vendor_ID
IN ( select Vendors.vendor_ID FROM Vendors
WHERE Vendors.vendor_state ='az' )

Results

Executing the query yields the results shown below where all vendors in Arizona whose invoices have been received are listed.

This image shows the query results in which only vendors in Arizona are included.